style24.doc/산출물/3.설계/03.인터페이스/더존/[NEOE].[UP_FASHION_FI_ADOCU_ASSET_I].sql

  
CREATE PROCEDURE [NEOE].[UP_FASHION_FI_ADOCU_ASSET_I]  
  
(  
  
 @CD_COMPANY NVARCHAR(7) = '1000'  
  
  , @DOCU_TYPE  CHAR(4) = 'BOS1'  
  
  , @SettleList SettleComplete READONLY  
  
  , @RESULT     INT OUT  
  
)  
  
AS  
  
  
  
  
/* FOR TEST  
DECLARE @CD_COMPANY NVARCHAR(7)  
DECLARE @Docu_Type CHAR(4)  
DECLARE @SettleList SettleComplete  
  
SET @CD_Company = '1000'  
SET @Docu_Type = 'BOS2'n  
insert into  @SettleList  
VALUES('1898700338','2019-05-31',-454700.0000,6230.0000,62295.0000,386175.0000)  
      
    
*/  
/*  
  
기존 5자리 계정코드에서 8자리 코드로 변경 2019 06 12  
  
  30310=>21102010 : 미지급금(거래처)  
  50100=>51102010 : 상품매출  
  30500=>21604010 : 부가세예수금  
  51100=>51107010 : 수수료매출  
  
  51102010<=>21604010  
  
  
*/  
/*  
담당자 사번 변경  
자금예정일자 추가   
*/  
DECLARE @CD_PC          NVARCHAR(7)  
  
DECLARE @CD_WDEPT       NVARCHAR(12)  
  
DECLARE @ID_WRITE       NVARCHAR(20)  
  
DECLARE @CD_CC   VARCHAR(6)  --추가2019 07 17  
  
DECLARE @UCD_MNG1  VARCHAR(3)  --추가2019 07 17  
  
DECLARE @ROW_ID_PREFIX CHAR(2)  
  
DECLARE @NM_ITEM_DESC   NVARCHAR(20)  
  
DECLARE @DT_END         VARCHAR(8) --추가 2020 02 20   
  
  
  
SET @CD_PC     = '1000'  
  
--SET @CD_WDEPT    = '1102'  
  
--SET @ID_WRITE    = '2017050102'  
  
SET @CD_WDEPT    = '15000' --변경 2019 07 17  
  
SET @ID_WRITE    = '2019071012'--변경 2019 07 17  2020 02 20 변경   
  
SET @CD_CC     = '500001'--추가2019 07 17  
  
SET @UCD_MNG1    = '890'--추가2019 07 17  
  
SET @DT_END = CONVERT(CHAR(6), GETDATE(), 112) + '15'--추가 2020 02 20 당월 15일로 세팅   
  
BEGIN   
  
 IF ISNULL(@DOCU_TYPE, 'BOS1') = 'BOS1'   
  
 BEGIN  
  
  SET @ROW_ID_PREFIX = 'IM'  
  
  SET @NM_ITEM_DESC = '월 상품판매수수료'  
  
 END  
  
 ELSE    
  
 BEGIN  
  
  SET @ROW_ID_PREFIX = 'ID'  
  
  SET @NM_ITEM_DESC = '월 배송비'  
  
 END  
  
  BEGIN TRY  
  
  IF @@TRANCOUNT > 0 BEGIN  
  
   SET @RESULT = @@TRANCOUNT  
  
  END  
  
  
  
   INSERT INTO NEOE.FI_ADOCU (ROW_ID, ROW_NO, NO_TAX, CD_PC, CD_WDEPT  
  
           , NO_DOCU, NO_DOLINE, CD_COMPANY, ID_WRITE, CD_DOCU  
  
           , DT_ACCT, ST_DOCU, TP_DRCR, CD_ACCT, AMT  
  
           , CD_PARTNER, NM_PARTNER, TP_JOB, CLS_JOB, ADS_HD  
  
           , NM_CEO, DT_START,DT_END, AM_TAXSTD, AM_ADDTAX, TP_TAX  
  
           , NO_COMPANY, NM_NOTE, CD_BIZAREA, CD_DEPT, CD_CC, NO_DEPOSIT--추가2019 07 17  
  
           , CD_BANK,UCD_MNG1, CD_MNG, TP_DOCU, NO_ACCT, TP_GUBUN--추가2019 07 17  
  
           , NM_ITEM1, NM_SIZE1, AM_SUPPLY1, AM_TAX1, NM_PUMM   
  
           , CD_MNGD1, CD_MNGD2, CD_MNGD3, CD_MNGD4, CD_MNGD5  
  
           , CD_MNGD6, CD_MNGD7, CD_MNGD8, NM_MNGD1, NM_MNGD2  
  
           , NM_MNGD3, NM_MNGD4, NM_MNGD5, NM_MNGD6, NM_MNGD7  
  
           , NM_MNGD8 )  
  
  SELECT @ROW_ID_PREFIX + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(100)) AS ROW_ID  
  
   , ROW_NUMBER() OVER(PARTITION BY S.BizNumber, S.SettleDate ORDER BY S.BizNumber, S.CD_ACCT) AS ROW_NO  
  
   , CASE WHEN S.CD_ACCT = '21604010'/*51102010*/ THEN 'T' + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(10)) ELSE '*' END AS NO_TAX   
  
   , @CD_PC AS CD_PC  
  
   , @CD_WDEPT AS CD_WDEPT  
  
  
  
   , @ROW_ID_PREFIX + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(100)) AS NO_DOCU  
  
   , ROW_NUMBER() OVER(PARTITION BY S.BizNumber, S.SettleDate ORDER BY S.BizNumber, S.CD_ACCT) AS NO_DOLINE  
  
   , @CD_PC AS CD_COMPANY  
  
   , @ID_WRITE AS ID_WRITE  
  
   , '11' AS CD_DOCU  
  
  
  
   , S.SettleDate AS DT_ACCT  
  
   , '1' AS ST_DOCU  
  
   , AC.TP_DRCR  
  
   , S.CD_ACCT   
  
   , ROUND(AMT, 0) AS AMT  
  
  
  
   , P.CD_PARTNER  
  
   , P.LN_PARTNER AS NM_PARTNER  
  
   , CONVERT(NVARCHAR(40), P.TP_JOB) AS TP_JOB  
  
   , CONVERT(NVARCHAR(40), P.CLS_JOB) AS CLS_JOB  
  
   , P.DC_ADS1_H AS ADS_HD  
  
  
  
   , P.NM_CEO  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN S.SettleDate ELSE '' END AS DT_START  
  
   ,@DT_END  AS DT_END--추가 2020 02 20  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN C.SupAmount ELSE 0 END AS AM_SUPPLY1  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN S.AMT ELSE 0 END AS AM_ADDTAX  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN '11' ELSE '' END AS TP_TAX  
  
  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN P.NO_COMPANY  ELSE '' END AS NO_COMPANY  
  
   , CASE WHEN @Docu_Type = 'BOS1'  THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 상품판매대금정산//'+ P.LN_PARTNER  
       WHEN @Docu_Type = 'BOS2'  THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 배송비정산//'+ P.LN_PARTNER END AS NM_NOTE --적요 수정 이선미   
  
  
   , @CD_PC AS CD_BIZAREA  
  
   , @CD_WDEPT AS CD_DEPT     
     
   , @CD_CC AS CD_CC   --추가2019 07 09  
   --, CASE WHEN S.CD_ACCT = '30500' THEN P.NO_DEPOSIT  ELSE '' END AS NO_DEPOSIT  
  
   , '' AS NO_DEPOSIT  
  
     
  
   --, CASE WHEN S.CD_ACCT = '30500' THEN P.CD_BANK  ELSE '' END AS CD_BANK  
  
   , '' AS CD_BANK  
     
   , @UCD_MNG1 AS UCD_MNG1   --추가2019 07 09  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN 'T' + S.SettleDate + REPLICATE('0', 5 - LEN(ROW_NO)) + CAST(ROW_NO AS VARCHAR(10)) ELSE '' END AS CD_MNG  
  
   , 'N' AS TP_DOCU  
  
   , 0 AS NO_ACCT  
  
   , '3' AS TP_GUBUN  
  
  
  
   , RIGHT(LEFT(S.SettleDate,6), 2) + @NM_ITEM_DESC AS NM_ITEM1  
  
   , '' AS NM_SIZE1  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN C.SupAmount ELSE 0 END AS AM_SUPPLY1  
  
   , CASE WHEN S.CD_ACCT = '21604010' THEN S.AMT ELSE 0 END AS AM_TAX1   
  
   , CASE WHEN @Docu_Type = 'BOS1'  THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 상품판매대금정산//'+ P.LN_PARTNER  
       WHEN @Docu_Type = 'BOS2'  THEN '[패션]'+CAST(MONTH(SettleDate) AS VARCHAR)+ '월 배송비정산//'+ P.LN_PARTNER END AS NM_PUMM --적요 수정      
  
  
  
   , CASE WHEN AC.ST_MNG1 = 'C' THEN CASE WHEN AC.CD_MNG1 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD1  
  
   , CASE WHEN AC.ST_MNG2 = 'C' THEN CASE WHEN AC.CD_MNG2 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD2  
  
   , CASE WHEN AC.ST_MNG3 = 'C' THEN CASE WHEN AC.CD_MNG3 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD3  
  
   , CASE WHEN AC.ST_MNG4 = 'C' THEN CASE WHEN AC.CD_MNG4 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD4  
  
   , CASE WHEN AC.ST_MNG5 = 'C' THEN CASE WHEN AC.CD_MNG5 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD5  
  
     
  
   , CASE WHEN AC.ST_MNG6 = 'C' THEN CASE WHEN AC.CD_MNG6 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD6  
  
   , CASE WHEN AC.ST_MNG7 = 'C' THEN CASE WHEN AC.CD_MNG7 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD7  
  
   , CASE WHEN AC.ST_MNG8 = 'C' THEN CASE WHEN AC.CD_MNG8 = 'A02' THEN '6000' ELSE '' END ELSE '' END AS CD_MNGD8  
  
   , CASE WHEN AC.ST_MNG1 = 'C' THEN CASE WHEN AC.CD_MNG1 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD1  
  
   , CASE WHEN AC.ST_MNG2 = 'C' THEN CASE WHEN AC.CD_MNG2 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD2  
  
  
  
   , CASE WHEN AC.ST_MNG3 = 'C' THEN CASE WHEN AC.CD_MNG3 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD3  
  
   , CASE WHEN AC.ST_MNG4 = 'C' THEN CASE WHEN AC.CD_MNG4 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD4  
  
   , CASE WHEN AC.ST_MNG5 = 'C' THEN CASE WHEN AC.CD_MNG5 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD5     
  
   , CASE WHEN AC.ST_MNG6 = 'C' THEN CASE WHEN AC.CD_MNG6 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD6  
  
   , CASE WHEN AC.ST_MNG7 = 'C' THEN CASE WHEN AC.CD_MNG7 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD7  
  
  
  
   , CASE WHEN AC.ST_MNG8 = 'C' THEN CASE WHEN AC.CD_MNG8 = 'A02' THEN '패션사업본부' ELSE '' END ELSE '' END AS NM_MNGD8  
  
  FROM(  
  
   SELECT ROW_NUMBER() OVER(ORDER BY BizNumber) AS ROW_NO   
  
      , BizNumber  
  
      , REPLACE(SettleDate,'-','') AS SettleDate  
       ,[상품매출액] as '51102010'  
       ,[예수부가세] as '21604010'  
       ,[수수료매출] as '51107010'  
       ,[미지급금]  as '21102010'  
     --  , CAST(ROUND((OrderSettleAmount) * -1 ,0) AS numeric(19, 4)) AS '51102010'--상품매출액  
     -- , CAST(ROUND((OrderSettleAmount + CouponPrice - CouponAllotment) * -1 ,0) AS numeric(19, 4)) AS '51102010'  
  
     --   , CAST(ROUND(MarginSettleAmount -( CouponPrice- CouponAllotment), 0) - ROUND((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1, 0) AS numeric(19,4)) AS '21604010'--예수부가세  
     -- , CAST(ROUND(MarginSettleAmount - CouponAllotment, 0) - ROUND((MarginSettleAmount - CouponAllotment) / 1.1, 0) AS numeric(19,4)) AS '21604010'  
  
       --   , CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS '51107010'--수수료매출  
    --  , CAST(ROUND(((MarginSettleAmount - CouponAllotment) / 1.1), 0) AS numeric(19, 4)) AS '51107010'  
  
    --  , CAST((Amount) AS numeric(19, 4)) AS '21102010'  
  
    FROM @SettleList  
  
    --WHERE OrderSettleAmount > 0  
  
  ) AS L  
  
  UNPIVOT  
  
  (  
  
   AMT FOR CD_ACCT IN ([21604010],[51102010],[51107010],[21102010])   
  
  ) AS S  
  
  CROSS APPLY(  
    
    SELECT [수수료매출] AS SupAmount  
   --SELECT CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS SupAmount  
  
   FROM @SettleList T  
  
   WHERE T.BizNumber = S.BizNumber  
  
   AND   REPLACE(T.SettleDate,'-','') = S.SettleDate  
  
  
  
  ) C  
  
  INNER JOIN [NEOE].[MA_PARTNER] P ON (S.BizNumber = P.NO_COMPANY AND P.CD_COMPANY = '1000')  
  
  LEFT OUTER JOIN NEOE.FI_ACCTCODE AC ON (AC.CD_ACCT = S.CD_ACCT AND AC.CD_COMPANY = '1000')    
  WHERE  ( @DOCU_Type = 'BOS2' AND s.CD_ACCT IN  (21102010, 51102010) ) OR  
  ( @DOCU_Type = 'BOS1' AND s.CD_ACCT IN  (21102010, 21604010,51102010,51107010) )   
   /*  
   30310:미지급금(거래처)  
   50100:상품매출  
   30500:부가세예수금  
   51100:수수료매출   
     
   ★코드변경됨★   
   21102010 미지급금(거래처)  
   21604010 예수부가세  
   51102010 상품매출액  
   51107010 수수료매출  
     
   상품판매대금정산과 배송비 정산 나오는 항목 수정   
   2019 05 30   
  
       
   */  
  IF @@ERROR > 0  
  
  BEGIN  
  
   SET @RESULT = -10004  
  
  END  
  
  ELSE  
  
  BEGIN  
  
   SET @RESULT = 10001  
  
  END  
  
  END TRY  
  
  BEGIN CATCH  
  
   SET @RESULT = -10004  
  
  END CATCH  
  
END  
  
  
  
RETURN